package jssia.database.mysql.preparedstatements;

import static jssia.database.mysql.databases.information_schema.Columns.CHARACTER_MAXIMUM_LENGTH;
import static jssia.database.mysql.databases.information_schema.Columns.COLLATION_NAME;
import static jssia.database.mysql.databases.information_schema.Columns.COLUMN_DEFAULT;
import static jssia.database.mysql.databases.information_schema.Columns.COLUMN_NAME;
import static jssia.database.mysql.databases.information_schema.Columns.COLUMN_TYPE;
import static jssia.database.mysql.databases.information_schema.Columns.DATA_TYPE;
import static jssia.database.mysql.databases.information_schema.Columns.EXTRA;
import static jssia.database.mysql.databases.information_schema.Columns.IS_NULLABLE;
import static jssia.database.mysql.databases.information_schema.Columns.NUMERIC_PRECISION;
import static jssia.database.mysql.databases.information_schema.Columns.NUMERIC_SCALE;
import static jssia.database.mysql.databases.information_schema.Columns.ORDINAL_POSITION;
import static jssia.database.mysql.databases.information_schema.Tables.ENGINE;
import static jssia.database.mysql.databases.information_schema.Tables.TABLE_COLLATION;
import static jssia.database.statement.Keywords.AND;
import static jssia.database.statement.Keywords.EQUALS;
import static jssia.database.statement.Keywords.FROM;
import static jssia.database.statement.Keywords.GREATER_THAN;
import static jssia.database.statement.Keywords.LEFT_JOIN;
import static jssia.database.statement.Keywords.ON;
import static jssia.database.statement.Keywords.ORDER_BY;
import static jssia.database.statement.Keywords.SELECT;
import static jssia.database.statement.Keywords.VALUE;
import static jssia.database.statement.Keywords.WHERE;
import static jssia.database.statement.Keywords._;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import jssia.database.mysql.databases.information_schema.Columns;
import jssia.database.mysql.databases.information_schema.Statistics;
import jssia.database.mysql.databases.information_schema.Tables;

public class DbInfoStatements {

    private static final String SELECT_DATABASE_COLLATION = "SELECT default_collation_name FROM information_schema.schemata WHERE schema_name=?";
    public static ResultSet selectDatabaseCollation(Connection conn, String dbName) throws SQLException {
        final PreparedStatement selectDatabaseCollationStatement = conn.prepareStatement(SELECT_DATABASE_COLLATION);
        selectDatabaseCollationStatement.setString(1, dbName);

        return selectDatabaseCollationStatement.executeQuery();
    }

    private static final String SELECT_TABLES_FOR_DB = SELECT + Tables.TABLE_NAME + _ + ENGINE + _ + TABLE_COLLATION + FROM + Tables.TO_STRING + WHERE + Tables.TABLE_SCHEMA + EQUALS + VALUE;
    public static ResultSet selectTablesForDatabase(Connection conn, String dbName) throws SQLException {
        PreparedStatement selectTablesForDatabaseStatement = conn.prepareStatement(SELECT_TABLES_FOR_DB);
        selectTablesForDatabaseStatement.setString(1, dbName);
        return selectTablesForDatabaseStatement.executeQuery();
    }

    private static final String SELECT_COLUMNS_FOR_TABLE =
        SELECT + COLUMN_NAME + _ + IS_NULLABLE + _ + DATA_TYPE + _ + CHARACTER_MAXIMUM_LENGTH + _ + COLLATION_NAME + _ + COLUMN_TYPE + _ + Statistics.NON_UNIQUE + _ + Statistics.INDEX_NAME + _ + Statistics.INDEX_TYPE + _ + EXTRA + _ + COLUMN_DEFAULT + _ + NUMERIC_PRECISION + _ + NUMERIC_SCALE
            + FROM + Columns.TO_STRING
                + LEFT_JOIN + Statistics.TO_STRING + ON + Statistics.TABLE_SCHEMA + EQUALS + Columns.TABLE_SCHEMA + AND + Statistics.TABLE_NAME + EQUALS + Columns.TABLE_NAME + AND + Statistics.COLUMN_NAME + EQUALS + Columns.COLUMN_NAME + AND + Statistics.SEQ_IN_INDEX + EQUALS + 1
                    + WHERE + Columns.TABLE_SCHEMA + EQUALS + VALUE + AND + Columns.TABLE_NAME + EQUALS + VALUE + ORDER_BY + ORDINAL_POSITION;

    public static ResultSet selectColumnsForTable(Connection conn, String dbName, String table) throws SQLException {
        PreparedStatement selectColumnsForTableStatement = conn.prepareStatement(SELECT_COLUMNS_FOR_TABLE);
        selectColumnsForTableStatement.setString(1, dbName);
        selectColumnsForTableStatement.setString(2, table);

        return selectColumnsForTableStatement.executeQuery();
    }

    private static final String SELECT_COLUMNS_FOR_INDEX = SELECT + Statistics.COLUMN_NAME
        + FROM + Statistics.TO_STRING
        + WHERE + Statistics.TABLE_SCHEMA + EQUALS + VALUE + AND + Statistics.TABLE_NAME + EQUALS + VALUE + AND + Statistics.INDEX_NAME + EQUALS + VALUE + AND + Statistics.SEQ_IN_INDEX + GREATER_THAN + 1
        + ORDER_BY + Statistics.SEQ_IN_INDEX;

    public static ResultSet selectColumnsForIndex(Connection conn, String dbName, String table, String indexName) throws SQLException {
        PreparedStatement selectColumnsForIndexStatement = conn.prepareStatement(SELECT_COLUMNS_FOR_INDEX);
        selectColumnsForIndexStatement.setString(1, dbName);
        selectColumnsForIndexStatement.setString(2, table);
        selectColumnsForIndexStatement.setString(3, indexName);

        return selectColumnsForIndexStatement.executeQuery();
    }
}
